package com.fsbm.ctech.maxis.ebpsbackend;

import java.io.*;
import java.text.*;
import java.util.*;
import java.util.Date;
import java.sql.*;

/**
 * Module Id    :
 * Description  :  The <code>InsertAcctExt</code> class insert Phone details in the AccountExt table.
 *
 * @version 1.00
 * @author  Jason R. Sarmiento
 *
 * @param phoneDtlFile  The Telephone detail file
 *
 * @return 1 for success and 0 for failure
 * @exception
 *
 * @Change History
 * @--------------
 * @Author      :
 * @Date        :
 * @Description :
 */


public class InsertAcctExt{



    private String[][] acctDtlArray = null;
	private PrintWriter logOut = null;
	private String exceptionFileName = "";
    private static String jdbcDriver = new String();
    private static String jdbcDataSource = new String();
    private static String username = new String();
    private static String password = new String();
    private boolean hasError = false;
    private PrintWriter exceptionOut =null;
    boolean firstTime=true;
    boolean exceptionThrown = false;

    private void getDbConfig(){
		try {
			Properties appCfg = new Properties();

			 FileInputStream inFile = new FileInputStream("/app/cfi/java/config.property");

// Poon : test on local
		    //FileInputStream inFile = new FileInputStream("D:\\Dev\\cfi\\v412\\java\\config.property");
		    appCfg.load(inFile);
	        inFile.close();

            jdbcDriver = appCfg.getProperty("databaseDriver");
            jdbcDataSource = appCfg.getProperty("databaseSourceName");
            username = appCfg.getProperty("databaseUserID");
            password = appCfg.getProperty("databasePassword");

			if (jdbcDriver ==null || jdbcDataSource==null || username==null || password==null)
				throw new Exception("DB Parameters not properly set");

        }
        catch (Exception e){
			hasError = true;
			System.out.println("Exception " + e);
		}
	}


    public void setPhoneDtl(String[][] acctDtlArray){
        this.acctDtlArray = acctDtlArray;
    }

	public void setLogStream(PrintWriter out){
        this.logOut = out;
    }

	public void setExceptionFileName(String expFileName){
        this.exceptionFileName = expFileName;
    }

    public boolean isFailed(){
        return (hasError);
    }

	private synchronized static void displayWriteToFile(PrintWriter out, String buffer){
        System.out.println(buffer);
        out.println(buffer);
    }

    public void runProg(){

	   //Counter
	   int InsertCount=0;
	   int UpdateCount=0;
	   int InsertAcctDtlCount=0;
	   int UpdateAcctDtlCount=0;

       displayWriteToFile(logOut, "BEGIN Insert/Update AccountExt\n");
            System.out.println("Begin at 0.....");
            // get database configuration
            getDbConfig();
       try{
            // load JDBC driver
            Class.forName(jdbcDriver);
            java.sql.Connection connection = java.sql.DriverManager.getConnection(jdbcDataSource, username, password);
            java.sql.Statement statement = connection.createStatement();
			java.sql.PreparedStatement pstmtQuery = null, pstmtInsert=null, pstmtUpdate=null;

            String querySql="SELECT AcctId FROM accountext WHERE AcctId= ? and PhoneNo=?";
		    String updateSql="UPDATE accountext SET Status=?, ServiceType=? WHERE  AcctId=? and PhoneNo=?";
		    String insertSql="INSERT into accountext (acctid, phoneno, status, servicetype) values(?,?,?,?)";

            String acctId="", phoneNo="", status="", serviceType=null;
			String acctKey = "", custGroup="",supIndicator="";
		    pstmtUpdate = connection.prepareStatement(updateSql);
            pstmtQuery = connection.prepareStatement(querySql);
            pstmtInsert = connection.prepareStatement(insertSql);
            for (int i=0; i < acctDtlArray.length; i++){

				try{
					if(exceptionThrown){
			            connection = java.sql.DriverManager.getConnection(jdbcDataSource, username, password);
			            statement = connection.createStatement();
					    pstmtUpdate = connection.prepareStatement(updateSql);
			            pstmtQuery = connection.prepareStatement(querySql);
			            pstmtInsert = connection.prepareStatement(insertSql);
			            exceptionThrown = false;
					}

                	acctId = acctDtlArray[i][0];
                	phoneNo = acctDtlArray[i][1];
                	status = acctDtlArray[i][2];
                	serviceType = acctDtlArray[i][3];
					acctKey = acctDtlArray[i][4];
					custGroup = acctDtlArray[i][5];
					//supIndicator = acctDtlArray[i][6];

					// AccountExt Start
					pstmtQuery.setString(1, acctId);
      				pstmtQuery.setString(2, phoneNo);


                	// check for duplicate
                	boolean result = false;

                	java.sql.ResultSet rs = pstmtQuery.executeQuery();;

                	while(rs.next()){
						String buff = rs.getString(1);
						if(buff.length() > 0){
						    result = true;
                	    }
						buff=null;
                	}
                	rs.close();

                	if (result == true){
						pstmtUpdate.setString(1, status);
      				    pstmtUpdate.setString(2, serviceType);
      				    pstmtUpdate.setString(3, acctId);
      				    pstmtUpdate.setString(4, phoneNo);

					    UpdateCount++;
                	    pstmtUpdate.executeUpdate();
                	    //displayWriteToFile(logOut, "Account : " +acctId+", Telephone : "+phoneNo+" updated\n");
					}else {
						pstmtInsert.setString(1, acctId);
      				    pstmtInsert.setString(2, phoneNo);
      				    pstmtInsert.setString(3, status);
      				    pstmtInsert.setString(4, serviceType);

                	    InsertCount++;
                	    pstmtInsert.executeUpdate();
					}
					rs=null;
					// AccountExt End

					// AccountDetails Start
					AcctDetails accDetails = new AcctDetails();

					if (accDetails.checkIfExistInAcctDetails(connection, acctId) == true) {
						if ((acctKey.equals("")) || (acctKey == null)) {
							accDetails.updateAcctDetails(connection, acctId,custGroup,"");
						} else {
							accDetails.updateAcctDetails(connection, acctId,acctKey,custGroup,"");
						}

					}
					else {
						accDetails.insertAcctDetails(connection, acctId,acctKey,custGroup);
					}
					// AccountDetails End

				}catch(Exception e){
					if(firstTime){
						exceptionOut = new PrintWriter(new java.io.BufferedWriter(new java.io.FileWriter(this.exceptionFileName)));
						firstTime = false;
					}
					displayWriteToFile(exceptionOut,"\nAccount ID is "+acctId+" and the exception is "+e);
					pstmtQuery.close();
					pstmtUpdate.close();
					pstmtInsert.close();
					connection.close();
					exceptionThrown=true;
				}
			}
			if(connection!=null){
				connection.commit();
				connection.close();
			}
			if(pstmtQuery!=null){
				pstmtQuery.close();
			}
			if(pstmtUpdate!=null){
				pstmtUpdate.close();
			}
			if(pstmtInsert!=null){
				pstmtInsert.close();
			}
		}
        catch (IllegalArgumentException iae){
            hasError = true;
			displayWriteToFile(logOut, "Error in configuration: " + iae +"\n");
        }
        catch (ClassNotFoundException cnfe){
            hasError = true;
            displayWriteToFile(logOut, "Error loading driver: " + cnfe +"\n");
        }
        catch (java.sql.SQLException sqle){
            hasError = true;
           displayWriteToFile(logOut, "Error connecting: " + sqle +"\n");
        }
        catch (Exception ex){
            hasError = true;
            displayWriteToFile(logOut, "-- EXCEPTION: " + ex.getClass() + " " + ex.getMessage() +"\n");
        }
        catch (Throwable t){
            hasError = true;
			displayWriteToFile(logOut, "-- EXCEPTION: " + t.getClass() + " " + t.getMessage() +"\n");
        }
		finally{
            displayWriteToFile(logOut, "END Insert/Update AccountExt\n");
            displayWriteToFile(logOut, "Total Records Inserted: "+InsertCount+"\n");
            displayWriteToFile(logOut, "Total Records Updated: "+UpdateCount+"\n");
            if(exceptionOut!=null){
				exceptionOut.close();
			}
		}
    }
}
